﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SQLite;
using System.IO;
using System.Windows.Forms;

namespace ALDIOrganizer
{
    /// <summary>
    /// This static class is responsible for managing SQLite databases
    /// </summary>
    /// <remarks>
    /// This class is in charge of managing the Employees, Holidays and datatables.
    /// The User CurrentFile (which contains ) is encrypted and is not an SQLite CurrentFile.
    /// It is managed by the UserDatabase class.
    /// </remarks>
    public static partial class SQLiteAccess
    {
        internal static DataView GetContractsDataView(Int64 empID)
        {
            return new DataView(
                _dataset.Tables[1],
                String.Concat(
                    "employeeID = ",
                    empID.ToString()),
                "date",
                DataViewRowState.CurrentRows);
        }

        internal static DataView GetHolidaysDataView(Int64 empID)
        {
            return new DataView(
                _dataset.Tables[2],
                String.Concat(
                    "employeeID = ",
                    empID.ToString(),
                    " AND type != ",
                    ((int)HolidayType.sick).ToString()),
                "date",
                DataViewRowState.CurrentRows);
        }

        internal static DataView GetHolidaysDataView(int year, Int64 empID, bool? pending, bool? finished)
        {
            var conditions = new List<String>();
            var filter = String.Empty;
            conditions.Add(
                "type <> " +
                ((int)HolidayType.sick)); // "sick" not to be shown
            conditions.Add(
                "startDate >= #" +
                new DateTime(year, 1, 1).ToShortDateString() +
                "#");
            conditions.Add(
                "startDate < #" +
                new DateTime(year + 1, 1, 1).ToShortDateString() +
                "#");

            if (empID >= 0)
            {
                conditions.Add("employeeID = " + empID.ToString());
            }
            if (finished.HasValue)
            {
                var _operator = finished.Value ? "<= " : "> ";
                conditions.Add(
                    "endDate " +
                    _operator +
                    "#" +
                    DateTime.Now.Date.ToString(
                    "d",
                    new System.Globalization.CultureInfo("EN-US")) + "#"); // EN-US, yes. SQLite requires American date format.
            }
            if (pending.HasValue)
            {
                conditions.Add("pending = " + (pending.Value ? "1" : "0"));
            }
            if (conditions.Count > 0)
            {
                filter = String.Join(" AND ", conditions.ToArray());
            }
            return new DataView(
                _dataset.Tables[2],
                filter,
                "startDate",
                DataViewRowState.CurrentRows);
        }

        internal static DataView GetRequestsDataView(
            Int64[] pEmpIDs,
            bool? pPending,
            bool? pPassed)
        {
            var _conditions = new List<String>();
            var _filter = String.Empty;

            if (!pEmpIDs.Contains(-1))
            {
                if (pEmpIDs.Length > 0)
                {
                    var _ids_as_string = (from _id in pEmpIDs
                                          select _id.ToString()).ToArray();
                    var _in = String.Join(
                        ", ",
                        _ids_as_string);
                    _conditions.Add("employeeID IN (" +
                        _in + ")");
                }
                else
                {
                    _conditions.Add("employeeID <> employeeID");
                }

            }
            if (pPassed.HasValue)
            {
                /* pPassed = true:      show ONLY requests that are no longer in force
                 * correct query:       (endDate isn't null AND <= today'pShift date)
                 * pPassed = false:     show ONLY requests that are in force
                 * correct query:       (endDate > today'pShift date OR is null) */

                var _operator = pPassed.Value ? "<= " : "> ";
                var _today = "#"
                    + DateTime.Now.Date.ToString("d", new System.Globalization.CultureInfo("EN-US"))
                    + "#";
                var _endDateIsEmpty = "endDate IS NULL";
                var _endDateHasValue = "NOT endDate IS NULL";

                // see above
                if (pPassed.Value)
                {
                    var _subcondition1 = " (" + _endDateHasValue + ") ";
                    var _subcondition2 = " (endDate <= " + _today + ") ";
                    _conditions.Add(
                        " ("
                        + _subcondition1
                        + " AND "
                        + _subcondition2
                        + ") ");
                }
                else // see above
                {
                    var _subcondition1 = " (" + _endDateIsEmpty + ") ";
                    var _subcondition2 = " (endDate > " + _today + ") ";
                    _conditions.Add(
                        " ("
                        + _subcondition1
                        + " OR "
                        + _subcondition2
                        + ") ");
                }

                // NOTE: if pPassed has no value, we don't need to add anything to query conditions
            }

            if (pPending.HasValue)
            {
                _conditions.Add(
                    "pending = "
                    + (pPending.Value ? "1" : "0"));
            }
            if (_conditions.Count > 0)
            {
                _filter = String.Join(
                    " AND ",
                    _conditions.ToArray());
            }

            return new DataView(
                _dataset.Tables[3],
                _filter,
                "startDate",
                DataViewRowState.CurrentRows);
        }

        internal static void SetFilterForStaffOnStaffTab(string pRowFilter)
        {
            var oldSort = _employeesForStaffTab.Sort; // we'll leave Sort alone (unchanged). so we're making a backup...

            /* since this method will re-new the dataview, we are passing
             * it the preserved old Sort value so that it can reassign it */
            SetEmployeesDataView(
                out _employeesForStaffTab,
                pRowFilter,
                oldSort);
        }

        internal static void SetFilterForStaffOnRequestsTab(string pRowFilter)
        {
            var oldSort = _employeesForRequestsTab.Sort; // we'll leave Sort alone (unchanged). so we're making a backup...

            /* since this method will re-new the dataview, we are passing
             * it the preserved old Sort value so that it can reassign it */
            SetEmployeesDataView(
                out _employeesForRequestsTab,
                pRowFilter,
                oldSort);
        }

        

        static void SetEmployeesDataView(out DataView pDataView, string RowFilter, string pSort)
        {            
            pDataView = new DataView(
                _dataset.Tables[0],
                RowFilter,
                pSort,
                DataViewRowState.CurrentRows);                        
        }

        private static DataView _employeesForStaffTab;
        private static DataView _employeesForRequestsTab;

        internal static BindingList<KeyValuePair<Int64, string>> EmployeesOnVisa(bool pOnVisa)
        {            
            var employees = (from row in GetEmployees()
                             where row.AsEmployee().currentEmployedNow &&
                             row.AsEmployee().OnVisa == pOnVisa
                             orderby row.AsEmployee().lastName, row.AsEmployee().firstName
                             select row.AsEmployee()).ToList();
            var keyvaluepairs = (from employee in employees
                                 select new KeyValuePair<Int64, string>
                                     (employee.ID,
                                     String.Concat(
                                        employee.displayColumn,
                                        " (",
                                        employee.currentContractHRS,
                                        " hours)").Replace(
                                        "\t",
                                        pOnVisa ? "\t" : " "))).
                                        ToArray();
            var result = new BindingList<KeyValuePair<Int64, string>>();
            foreach (var pair in keyvaluepairs)
            {
                result.Add(pair);
            }
            return result;
        }

        internal static DataView EmployeesForStaffTab
        {
            get
            {
                return _employeesForStaffTab;
            }
        }
        
        internal static DataView EmployeesForRequestsTab
        {
            get
            {
                return _employeesForRequestsTab;
            }
        }

    }
}
